import glob
import os
import shutil
import pymysql

names = "billboards,airdefensefortifications,anquandao,bumingcovers,dlssbzp,chongdianzhuang,dlcovers,crossingbridge,dianlilg,dimingpai,dlights,bmlg,dulishu,electroniceye,envirmonitoringstation,fangzhuangtong,floodwall,flowerpot,fountain,gacovers,dscovers,ldcovers,jietouzuoyi,kuohegd,landscape,ldfsss,lvdihulan,oldvaluabletrees,garbageroom,hwjsss,qigan,publicbasement,publictoilets,pylons,roadbrand,rouxinggeliti,rxhdz,rqcovers,shayijia,shuijing,shuyoubz,sybzp,toiletsigns,syqcovers,trafficbarrier,trafficlights,sculpture,transformerbox,treecarefacilities,watersfence,waterer,weatherstations,wlcovers,xgjbz,xhddycovers,trafficsignals,yscovers,zhanqiao,zzjfj,parkingmeters,xiuxiting,ylcovers".split(
    ",")
shp_dir = r"C:\Users\17673\Downloads\赣州有改动数据\rename\*"

not_in_dir = r"C:\Users\17673\Downloads\赣州有改动数据\notin"


def diff(names, shp_dir, not_in_dir):
    for path in glob.glob(shp_dir):
        file_name = os.path.basename(path)
        # print(file_name)
        if file_name.split(".")[0] not in names:
            target_file = not_in_dir + "\\" + file_name
            print(path + "   " + target_file)
            shutil.copy(path, target_file)


def diff2():
    gzcg_new_tables = "billboards,airdefensefortifications,anquandao,bumingcovers,dlssbzp,chongdianzhuang,dlcovers,crossingbridge,dianlilg,dimingpai,dlights,bmlg,dulishu,electroniceye,envirmonitoringstation,fangzhuangtong,floodwall,flowerpot,fountain,gacovers,dscovers,ldcovers,jietouzuoyi,kuohegd,landscape,ldfsss,lvdihulan,oldvaluabletrees,garbageroom,hwjsss,qigan,publicbasement,publictoilets,pylons,roadbrand,rouxinggeliti,rxhdz,rqcovers,shayijia,shuijing,shuyoubz,sybzp,toiletsigns,syqcovers,trafficbarrier,trafficlights,sculpture,transformerbox,treecarefacilities,watersfence,waterer,weatherstations,wlcovers,xgjbz,xhddycovers,trafficsignals,yscovers,zhanqiao,zzjfj,parkingmeters,xiuxiting,ylcovers,biandaozhuang,busstation,fjdctfd,hangshu,tongxinlg,trafficsigns,trash,txjjx,wscovers,firefacilities,hwgg,lights,parkinglot,pbbz,powerfacilities,sscovers,tongxincovers,ysbz".split(
        ",")
    gzcg_old_tables = "airdefensefortifications,anquandao,biandaozhuang,biaozhixingjianzhuwu,billboards,bmlg,bumingcovers,busstation,cell,chongdianzhuang,community,dianlancovers,dianlilg,dlssbzp,dimingpai,dlcovers,dlights,dscovers,dulishu,electroniceye,envirmonitoringstation,firefacilities,flowerpot,fountain,gacovers,garbageroom,gasregulatorbox,hfccovers,gatehouseaddress,gccsgb,gjcovers,gongde,huayouchi,hwgg,hwjsss,informationkiosks,jiaotongsheshi,kiosk,landscape,ldfsss,lijiaoqiao,ltcc,lvdihulan,newsstand,ouheou,noisedisplay,oldvaluabletrees,parkinglot,parkingmeters,pbbz,portinspectorfacilities,powerfacilities,publicbasement,publictoilets,qigan,qt,qtgyss,rlcovers,rxhdz,rouxinggeliti,rqcovers,ruleguard,septic,sewagestations,shayijia,shiheou,shirongweishengzerenpai,shiwaishuibiao,shuijing,sscovers,storagebracket,street,trafficbarrier,sybzp,taxisign,telephonebooth,toiletsigns,trafficlights,trafficsentrybox,trafficsignals,trafficsigns,transformerbox,trash,tsqcovers,treecarefacilities,underpass,vendingmachine,waterer,watersancillaryfacilities,watersfence,weatherstations,wlcovers,wscovers,wsjcq,wwgj,xhddycovers,xiuxiting,xingqudian,yhqz,yingjibinanzhishipai,ylcovers,zhanqiao,zzjfj,zscovers,zxczld,zrwg,jsqy".split(
        ",")
    gzcg_old_tables.sort()
    gzcg_new_tables.sort()
    print("新图层个数", len(gzcg_new_tables))
    print("旧图层个数", len(gzcg_old_tables))
    print(gzcg_old_tables)
    print(gzcg_new_tables)

    print(len(set(gzcg_new_tables) - set(gzcg_old_tables)))
    path = r"C:\Users\17673\Downloads\sql"
    for name in set(gzcg_new_tables) - set(gzcg_old_tables):
        print(name)
        new_path = path + "\\notin\\" + name + ".sql"
        old_path = path + "\\" + name + ".sql"
        # shutil.copy(old_path, new_path)


def eport_pg_table():
    # cmd_str = "pgsql2shp.exe -f C:\Users\Administrator\Desktop\gzbj_xg\olfshp\cell.shp -h 172.16.50.17 -u postgres -P egova -p 5432 gzcg2023 cell"
    cmd_str = r"pgsql2shp.exe -f C:\Users\Administrator\Desktop\gzbj_xg\olfshp\{0}.shp -h 172.16.50.17 -u postgres -P egova -p 5432 gzcg2023 {1}"
    gzcg_old_tables = "airdefensefortifications,anquandao,biandaozhuang,biaozhixingjianzhuwu,billboards,bmlg,bumingcovers,busstation,cell,chongdianzhuang,community,dianlancovers,dianlilg,dlssbzp,dimingpai,dlcovers,dlights,dscovers,dulishu,electroniceye,envirmonitoringstation,firefacilities,flowerpot,fountain,gacovers,garbageroom,gasregulatorbox,hfccovers,gatehouseaddress,gccsgb,gjcovers,gongde,huayouchi,hwgg,hwjsss,informationkiosks,jiaotongsheshi,kiosk,landscape,ldfsss,lijiaoqiao,ltcc,lvdihulan,newsstand,ouheou,noisedisplay,oldvaluabletrees,parkinglot,parkingmeters,pbbz,portinspectorfacilities,powerfacilities,publicbasement,publictoilets,qigan,qt,qtgyss,rlcovers,rxhdz,rouxinggeliti,rqcovers,ruleguard,septic,sewagestations,shayijia,shiheou,shirongweishengzerenpai,shiwaishuibiao,shuijing,sscovers,storagebracket,street,trafficbarrier,sybzp,taxisign,telephonebooth,toiletsigns,trafficlights,trafficsentrybox,trafficsignals,trafficsigns,transformerbox,trash,tsqcovers,treecarefacilities,underpass,vendingmachine,waterer,watersancillaryfacilities,watersfence,weatherstations,wlcovers,wscovers,wsjcq,wwgj,xhddycovers,xiuxiting,xingqudian,yhqz,yingjibinanzhishipai,ylcovers,zhanqiao,zzjfj,zscovers,zxczld,zrwg,jsqy".split(
        ",")
    print(len(gzcg_old_tables))
    for gzcg_old_table in gzcg_old_tables:
        print(cmd_str.format(gzcg_old_table, gzcg_old_table))


def rename_shp_phylayer(shp_path, target_path):
    connection = pymysql.connect(
        host="localhost",
        database="test",
        user="root",
        password="egova",
        port=3306
    )
    cursor = connection.cursor()
    query_sql_detail = '''select phy_layer_name, phy_layer_tname, service_layer_id from tc_phy_layer'''
    cursor.execute(query_sql_detail)
    all_rec = cursor.fetchall()
    name_dic = {}
    for row in all_rec:
        phy_layer_name, phy_layer_tname, service_layer_id = row
        # print(phy_layer_name, phy_layer_tname, service_layer_id)
        name_dic[phy_layer_name] = service_layer_id

    print(name_dic.keys())

    for path in glob.glob(shp_path + "\\*"):
        file_name = os.path.basename(path)
        base_name = file_name.split(".")[0]
        if base_name not in name_dic.keys() and os.path.isfile(path):
            target_file = os.path.join(target_path, file_name)
            # shutil.copy(path, target_file)
            # print(path, target_file)
            # print(base_name)
        if os.path.isfile(path) and file_name.split(".")[1] == "shp":
            # print(base_name)
            print("DROP TABLE IF EXISTS {0};".format(base_name))


if __name__ == '__main__':
    # diff2()
    # eport_pg_table()
    # rename_shp_phylayer(r"C:\Users\17673\Downloads\赣州有改动数据", r"C:\Users\17673\Downloads\赣州有改动数据\rename_phy")
    rename_shp_phylayer(r"E:\现场数据\部件更新\更新\未按phylayer表重命名结果", r"E:\现场数据\部件更新\更新\按phylayer表重命名结果")
